﻿//////////////////////elaborado por: Susana Taquichiri////////////////////////
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Boa.AIT.Negocios.Utils
{
    public class LectorExcel
    {
        public List<string> ObtenerValores(string pathArchivo)
        {
            string path = System.IO.Path.GetFullPath(pathArchivo);
            string connString = "provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=" + path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection oledbConn = new OleDbConnection(connString);

            List<string> lista = new List<string>();

            try
            {
                oledbConn.Open();

                DataTable dtExcelSchema;
                dtExcelSchema = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);


                string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

                OleDbCommand cmd = new OleDbCommand("SELECT * From [" + sheetName + "]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;

                //DataSet ds = new DataSet();
                //oleda.Fill(ds, "Table");

                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string str = reader.GetValue(0).ToString();
                    if (!string.IsNullOrEmpty(str))
                        lista.Add(str);

                }
                reader.Close();
                cmd.Dispose();

                //GridView1.DataSource = ds.Tables[0].DefaultView;
                //GridView1.DataBind();
            }
            catch
            {
            }
            finally
            {
                oledbConn.Close();
            }

            return lista;

        }

    }
}
